﻿using System;
using System.Collections.Generic;
using System.Text;
using XiaoGang.Soft.XGWMS.COM;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_System_SQL
    {
        #region SQL 语句

        #region 

        private static string _getGarbageData_SQL = @"select CREATE_DATETIME
,UPDATER_ID
,TERMINAL_CD
BILL_ID
,(Case IN_OUT When '0' Then '进货' When '1' Then '销售' When '2' Then '盘库' End) As IN_OUT
/*0为入库，1为出库，2为盘库*/
,BUSINESS_TIME
,MEMO
,USER_NO
From dbo.M010_BILL
Where bill_id Not In (
Select Distinct bill_id From dbo.M009_STORAGE)
Order By CREATE_DATETIME,IN_OUT";

        #endregion

        #region

        /// <summary>
        /// 获取垃圾数据
        /// </summary>
        public static string GetGarbageData
        {
            get { return _getGarbageData_SQL; }
        }

        #endregion

        #region 清除冗余货单信息

        private static string _deleteBillGarbage = @"Delete FROM M010_BILL
WHERE BILL_ID NOT IN(SELECT BILL_ID FROM M009_STORAGE )";

        #endregion

        #region 清除冗余商品信息[已删除并且没有销售记录]

        private static string _deleteGoodsGarbage = @"Delete FROM dbo.M007_GOODS
WHERE TRANS_STATE ='3' 
AND GOODS_ID NOT IN(SELECT DISTINCT GOODS_ID FROM dbo.M009_STORAGE)";

        #endregion

        #region 清除冗余商品分类信息[已删除并且没有被使用]

        private static string _deleteGoodsGroupGarbage = @"Delete FROM M006_GOODS_GROUP
WHERE GOODS_GROUP_ID NOT IN(SELECT GOODS_GROUP FROM M007_GOODS) AND TRANS_STATE = '3' ";
        #endregion

        #region 清除冗余会员信息[已删除并且没有消费记录]

        private static string _deleteMemberGarbage = @"Delete FROM M012_MEMBER
WHERE USER_NO NOT IN (SELECT DISTINCT USER_NO FROM M010_BILL WHERE USER_NO <> '')
AND USER_NO NOT IN (SELECT DISTINCT USER_ID FROM dbo.M014_MerberBuy WHERE USER_ID <> '')
AND TRANS_STATE ='3' ";
        #endregion

        #region 数据库备份

        private static string _backUp = @"backup database @DataBaseName to disk=@Path";

        #endregion

        #region 数据库还原

        private static string _reStore = @"ALTER DATABASE @DataBaseName SET ONLINE 
ALTER DATABASE @DataBaseName SET OFFLINE 
RESTORE DATABASE @DataBaseName FROM DISK = @Path WITH NORECOVERY, REPLACE 
RESTORE DATABASE @DataBaseName WITH RECOVERY ";

        #endregion


        #endregion

        #region 属性

        /// <summary>
        /// 清除冗余货单信息
        /// </summary>
        public static string DeleteBillGarbage
        {
            get { return _deleteBillGarbage; }
        }

        /// <summary>
        /// 清除冗余商品信息[已删除并且没有销售记录]
        /// </summary>
        public static string DeleteGoodsGarbage
        {
            get { return _deleteGoodsGarbage; }
        }

        /// <summary>
        /// 清除冗余商品分类信息[已删除并且没有被使用]
        /// </summary>
        public static string DeleteGoodsGroupGarbage
        {
            get { return _deleteGoodsGroupGarbage; }
        }

        /// <summary>
        /// 清除冗余会员信息[已删除并且没有消费记录]
        /// </summary>
        public static string DeleteMemberGarbage
        {
            get { return _deleteMemberGarbage; }
        }

        /// <summary>
        /// 数据库备份
        /// </summary>
        public static string BackUp
        {
            get { return _backUp; }
        }

        /// <summary>
        /// 数据库还原
        /// </summary>
        public static string ReStore
        {
            get { return _reStore; }
        }

        #endregion
    }
}
